# coding: UTF-8
# fileName: hotelsLogDBSave.py

import MySQLdb
import codecs
import time, datetime
import sys,os
import traceback
import threading
import createTable
reload(sys) 
sys.setdefaultencoding('utf-8')
#获取当前执行文件的绝对路径
linux_file_path = sys.path[0] + os.sep + 'jiudian_init.ini';
#读取配置文件   
fileList = []
fileUrl = createTable.readFromIni(linux_file_path,'fileUrl').strip()
fileListStr = createTable.readFromIni(linux_file_path,'fileList').strip()
fileListArr = fileListStr.split('|')
for flieListItem in fileListArr:
    files = flieListItem.split(',')    
    fileList.append([files[0].replace("'","").replace("\"",""),files[1].replace("'","").replace("\"","")])
tableHead = createTable.readFromIni(linux_file_path,'tableHead').strip() #表前缀   flighttest2010-09-1:上旬  flighttest2010-09_2:中旬  flighttest2010-09_3:下旬
charset = createTable.readFromIni(linux_file_path,'charset').strip()
fromMail = createTable.readFromIni(linux_file_path,'fromMail').strip()
mailserver = createTable.readFromIni(linux_file_path,'mailserver').strip()
to_emails = createTable.readFromIni(linux_file_path,'to_emails').strip()
url = createTable.readFromIni(linux_file_path,'url').strip()
database = createTable.readFromIni(linux_file_path,'database').strip()
username = createTable.readFromIni(linux_file_path,'username').strip()
password = createTable.readFromIni(linux_file_path,'password').strip()


#获取输入的日期
startDate=''
endDate=''
yestoday = ''

if len(sys.argv) >= 2:
    startDate = sys.argv[1]
    if len(sys.argv)>2:
        endDate=sys.argv[2]
else:
    tody = time.strptime(str(time.strftime('%Y-%m-%d')), "%Y-%m-%d")
    startDate = str(datetime.datetime(tody[0], tody[1], tody[2]) - datetime.timedelta(days=1)).split(' ')[0]


day=[];
oneday=datetime.timedelta(days=1);
if len(endDate.strip())!=0:
    endDate = time.strptime(endDate, "%Y-%m-%d")
    startDate=time.strptime(startDate,"%Y-%m-%d")
    endDate=datetime.datetime(endDate[0], endDate[1], endDate[2])
    startDate=datetime.datetime(startDate[0], startDate[1], startDate[2])
    if endDate<startDate:
        print 'Start date can not be greater than the end date'
    else :
        while startDate<=endDate:
            myday=str(startDate).split(' ')[0];
            day.append(myday);
            startDate=startDate+oneday
else:
    day.append(startDate);
    
#    发送邮件  
def sendMail(subject,content):
    os.system('sendemail -xu \"warning\" -xp \"oyesgo2010\" -f \"'+fromMail+'\" -t \"'+to_emails+'\" -s \"'+mailserver+'\" -u \"'+subject+'\" -o "message-charset=UTF-8" -m \"'+content+'\"')    
    
#获取数据库连接    
def createConnection():
    conn = MySQLdb.Connection(url, username, password, database, use_unicode=True,charset='utf8')
    return conn
#插入操作
def insertLog(_sql, _list,tablename,eday):
    conn = createConnection();
    try:
        curs = conn.cursor()
        try:
            for i in _list:
                curs.execute(_sql,i) 
            conn.commit()
            #调    java代码产生lucene索引
        except:
            status = 'false'
            print m
            print j
            conn.rollback()
            print traceback.format_exc()
            sendMail('插入数据库失败', '异常params: %s' %(_list));
            print 'save error%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%'
        finally:
            curs.close()
    finally:
        conn.close()
        
#插入操作线程
class ThreadInsert(threading.Thread):
    def __init__(self,insert_sql,param,name,date):
        threading.Thread.__init__(self)
        self.insert_sql = insert_sql
        self.param = param
        self.name = name
        self.date = date
        
    def run(self):
        insertLog(self.insert_sql,self.param,self.name,self.date)
  
#检查指定日期数据是否存在 
def checkDataIsExist(date,tablename,page_type):
    sql = "select * from "+tablename+" where save_date like '"+date +"%' and page_type like '"+page_type+"%'"
    conn = createConnection()
    try:
        curs = conn.cursor()
        try:
            curs.execute(sql)
            yestodaylist = curs.fetchall()
            if len(yestodaylist)>0:
                return True
            else:
                return False
        except:
            print traceback.format_exc()
        finally:
            curs.close()
    except:
        print traceback.format_exc()
    finally:conn.close()
#截取指定长度的字符
def getSpecifiedLen(obj,lenths):
    if len(obj)>lenths:
        return obj[0:lenths]
    else:
        return obj

def setZero(obj):
    if obj==None or len(obj) == 0:
        obj = '0.0'
    return obj

         

#入库操作     
for daynow in day:
    tableName = createTable.returnTable(tableHead,daynow) #创建表(没有就创建)
    for fileLine in fileList:
        reslist =codecs.open(fileUrl+fileLine[0]+"."+daynow,'r',charset)
        insert_sql = 'insert into '+tableName+' values(%s, %s,%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)';
        errList = []
        start_time = str(datetime.datetime(*time.localtime()[:6]))
        print 'start time: %s' %start_time
        #if checkDataIsExist(daynow, tableName,fileLine[1]):
            #print fileLine[1] + "  "+ daynow+' data is exist'
            #continue
        try:
            eday = ''
            formatstr='%Y-%m-%d_%H:%M:%S'
            sqlList = []
            i = 0
            for line in reslist:
                try:
                    t = line.split('] [')  #17个：没有返程日  18个有返程日
                    if t[1].find('INFO')==-1:#不是INFO的直接过滤掉
                        continue
                    #日记文件日期与日志不符退出
                    t[0] = t[0].replace('[','').replace('_',' ')
                    i = i+1
                    if i == 1:
                        eday = t[0].split(' ')[0]
                        edayArr = time.strptime(eday, '%Y-%m-%d')
                        realDay = datetime.datetime(edayArr[0],edayArr[1],edayArr[2])
                        sysdayArr = time.strptime(daynow, '%Y-%m-%d')
                        realSysDay = datetime.datetime(sysdayArr[0],sysdayArr[1],sysdayArr[2])
                        if realSysDay!=realDay:
                            print 'sys_date is not equal with log_date'
                            sqlList = []
                            break
                    if len(t)<12:#小于12位的是错误日志
                        continue
                    #超过指定长度的区部截掉  基本数据部分  
                    t[2] =  getSpecifiedLen(t[2].replace('ip_source:',''), 32)
                    t[3] =  getSpecifiedLen(t[3].replace('user_agent:',''), 512)
                    t[4] =  getSpecifiedLen(t[4].replace('localUrl:',''), 512)
                    t[5] =  getSpecifiedLen(t[5].replace('ref:',''), 64)
                    t[6] =  getSpecifiedLen(t[6].replace('outerUrl:',''), 512)
                    t[7] =  getSpecifiedLen(t[7].replace('innerUrl:',''), 512)
                    t[8] =  getSpecifiedLen(t[8].replace('userid:','').replace(']',''), 512)
                    t[len(t)-1] = t[len(t)-1].replace(']','')
                    if len(t) ==12 and t[9].find(fileLine[1])>-1:#只有基本数据的
                        #modified by rose
                        if t[9].find('JIUDIAN') > -1 and t[4].find('http://jiudian.soulv.com/jump.html') > -1:#如果localUrl是中间跳转页
                            temp_flag = t[4]
                            local_url_paramer = temp_flag.replace('http://jiudian.soulv.com/jump.html?','')
                            local_url_paramer_strs = local_url_paramer.split('&') #获取参数数组
                            provider = local_url_paramer_strs[0].replace('p=','')
                            hotel_addr = local_url_paramer_strs[1].replace('hn=','')
                            leave_date = local_url_paramer_strs[2].replace('checkin=','')
                            back_date = local_url_paramer_strs[3].replace('checkout=','')
                            tempSqlTuple = [None,t[0].strip(),t[2].strip(),t[3].strip(),t[4].strip(),t[5].strip(),t[6].strip(),t[7],t[8].strip(),t[9].strip(),t[10].strip(),t[11].strip(),daynow,None,None,None,None,leave_date,back_date,provider,None,None,None,None,None,None,None,None,hotel_addr,None,None,None,None,None,None,None]
                            sqlList.append(tempSqlTuple)
                        else:
                            tempSqlTuple = [None,t[0].strip(),t[2].strip(),t[3].strip(),t[4].strip(),t[5].strip(),t[6].strip(),t[7],t[8].strip(),t[9].strip(),t[10].strip(),t[11].strip(),daynow,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None,None]
                            sqlList.append(tempSqlTuple)
                    elif len(t)>12:
                         if len(t)==21 and t[9].find(fileLine[1])>-1 and t[9].find('JIUDIAN')>-1 and t[11].find('ORDER')>-1:#酒店预ORDER
                            price = 0.0
                            t[19] = setZero(t[19])
                            t[20] = setZero(t[20])
                            if len(t[19])>0 and float(t[19])>0.0:
                                 price = float(t[19])
                            elif len(t[20])>0 and float(t[20])>0.0:
                                 price = float(t[20])   
                            tempSqlTuple = [None,t[0].strip(),t[2].strip(),t[3].strip(),t[4].strip(),t[5].strip(),t[6].strip(),t[7].strip(),t[8].strip(),t[9].strip(),t[10].strip(),t[11].strip(),daynow,None,None,None,None,t[16].strip(),t[17].strip(),t[12].strip(),price,None,None,None,None,None,t[14].strip(),None,t[13].strip(),t[15].strip(),t[18].strip(),None,None,None,None,None] 
                            sqlList.append(tempSqlTuple)
                         elif len(t)==22 and t[9].find(fileLine[1])>-1 and t[9].find('JIUDIAN')>-1 and t[11].find('ORDER')>-1:#酒店预ORDER
                            price = 0.0
                            t[19] = setZero(t[19])
                            t[20] = setZero(t[20])
                            t[21] = setZero(t[21])
                            if len(t[19])>0 and float(t[19])>0.0:
                                 price = float(t[19])
                            elif len(t[20])>0 and float(t[20])>0.0:
                                 price = float(t[20])
                            elif len(t[21])>0 and float(t[21])>0.0:
                                 price = float(t[21])    
                            tempSqlTuple = [None,t[0].strip(),t[2].strip(),t[3].strip(),t[4].strip(),t[5].strip(),t[6].strip(),t[7].strip(),t[8].strip(),t[9].strip(),t[10].strip(),t[11].strip(),daynow,None,None,None,None,t[16].strip(),t[17].strip(),t[12].strip(),price,None,None,None,None,None,t[14].strip(),None,t[13].strip(),t[15].strip(),t[18].strip(),None,None,None,None,None] 
                            sqlList.append(tempSqlTuple)
                         elif len(t)==20 and t[9].find(fileLine[1])>-1 and t[9].find('JIUDIAN')>-1 and t[11].find('ORDER')>-1:#酒店预ORDER
                            price = 0.0
                            t[19] = setZero(t[19])
                            if len(t[19])>0 and float(t[19])>0.0:
                                 price = float(t[19])
                            tempSqlTuple = [None,t[0].strip(),t[2].strip(),t[3].strip(),t[4].strip(),t[5].strip(),t[6].strip(),t[7].strip(),t[8].strip(),t[9].strip(),t[10].strip(),t[11].strip(),daynow,None,None,None,None,t[16].strip(),t[17].strip(),t[12].strip(),price,None,None,None,None,None,t[14].strip(),None,t[13].strip(),t[15].strip(),t[18].strip(),None,None,None,None,None] 
                            sqlList.append(tempSqlTuple)
                         elif len(t)==23 and t[9].find(fileLine[1])>-1 and t[9].find('JIUDIAN')>-1 and t[11].find('ORDER')>-1:#酒店预ORDER
                            price = 0.0
                            t[19] = setZero(t[19])
                            t[20] = setZero(t[20])
                            t[21] = setZero(t[21])
                            if len(t[19])>0 and float(t[19])>0.0:
                                 price = float(t[19])
                            elif len(t[20])>0 and float(t[20])>0.0:
                                 price = float(t[20])
                            elif len(t[21])>0 and float(t[21])>0.0:
                                 price = float(t[21])
                            elif len(t[22])>0 and float(t[22])>0.0:
                                 price = float(t[22])      
                            tempSqlTuple = [None,t[0].strip(),t[2].strip(),t[3].strip(),t[4].strip(),t[5].strip(),t[6].strip(),t[7].strip(),t[8].strip(),t[9].strip(),t[10].strip(),t[11].strip(),daynow,None,None,None,None,t[16].strip(),t[17].strip(),t[12].strip(),price,None,None,None,None,None,t[14].strip(),None,t[13].strip(),t[15].strip(),t[18].strip(),None,None,None,None,None] 
                            sqlList.append(tempSqlTuple)
                         elif len(t)==14 and t[9].find(fileLine[1])>-1 and t[9].find('JIUDIAN')>-1 and t[11].find('ORDER')==-1:#酒店预定
                            tempSqlTuple = [None,t[0].strip(),t[2].strip(),t[3].strip(),t[4].strip(),t[5].strip(),t[6].strip(),t[7],t[8].strip(),t[9].strip(),t[10].strip(),'ORDER',daynow,None,None,None,None,t[12].strip(),t[13].strip(),None,None,None,None,None,None,None,t[11].strip(),None,None,None,None,None,None,None,None,None] 
                            sqlList.append(tempSqlTuple)   
                         elif len(t)==15 and t[9].find(fileLine[1])>-1 and t[9].find('JIUDIAN')>-1 and  t[11].find('SEARCH')==-1:#酒店查询
                            tempSqlTuple = [None,t[0].strip(),t[2].strip(),t[3].strip(),t[4].strip(),t[5].strip(),t[6].strip(),t[7],t[8].strip(),t[9].strip(),t[10].strip(),'SEARCH_11',daynow,None,None,None,None,t[12].strip(),t[13].strip(),None,None,None,None,None,None,None,t[11].strip(),None,t[14].strip(),None,None,None,None,None,None,None] 
                            sqlList.append(tempSqlTuple)
                         else:
                              print len(t)
                              print t   
                except:
                    print 'encode line' +''+ 'err'
                    print traceback.format_exc()
                    sendMail('read log err', 'read log err '+fileLine[0]+ daynow)
                    #发送邮件
                if i%2000==0:
                    thread = ThreadInsert(insert_sql,sqlList,tableName,eday)
                    thread.start()
                    sqlList = []
            thread = ThreadInsert(insert_sql,sqlList,tableName,eday)
            thread.start()
            sqlList = []
        except:
            print 'read ' + 'search_'+eday+'.log' + ' error'
            print traceback.format_exc()
            sendMail("read log err", 'read log err  jiudian.log.'+daynow)
        finally:
            reslist.close()
            while threading.activeCount()!=1:
                time.sleep(1);
                continue
            end_time = str(datetime.datetime(*time.localtime()[:6]))
            print 'end time: %s' %end_time
            
   
    